﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Serialization;
using Cinchoo.Core.IO;
using Cinchoo.Core.Collections;
using System.IO;
using System.Collections;
using System.Data.SqlClient;
using System.Threading;
using System.Data;
using System.Text.RegularExpressions;
using System.Data.SQLite;

namespace Cinchoo.Core.ETL
{
    public abstract class ChoETLBaseDbCommandTask : ChoETLTask
    {
        #region Instance Data Members (Private)

        protected bool _selfManage = false;
        protected string _connectionString;
        protected string _sql;

        protected IDbConnection _connection;
        protected IDbCommand _command;
        protected IDbTransaction _trans;

        #endregion Instance Data Members (Private)

        #region Constructors

        public ChoETLBaseDbCommandTask(string connectionString, string sql)
        {
            ChoGuard.ArgumentNotNullOrEmpty(sql, "Sql");

            _connectionString = connectionString;
            _sql = sql;
            _selfManage = true;
        }

        public ChoETLBaseDbCommandTask(IDbConnection connection, string sql, IDbTransaction trans = null)
        {
            ChoGuard.ArgumentNotNullOrEmpty(connection, "Connection");
            ChoGuard.ArgumentNotNullOrEmpty(sql, "Sql");

            _connection = connection;
            _sql = sql;
            _trans = trans;
            _selfManage = false;
        }

        #endregion Constructors

        #region ChoETLFileLoadTask Overridables

        protected override bool Initialize()
        {
            if (_selfManage)
            {
                IDbConnection conn = ETLRuntime.ResolveToScalarObject(_connectionString) as IDbConnection;
                if (conn == null)
                    throw new ChoETLException("Missing connection object.");

                if (conn == null)
                {
                    _connection.Open();
                    _trans = _connection.BeginTransaction();
                }
                else
                {
                    _connection = conn;
                    _trans = ETLRuntime.ResolveToScalarObject("{0}_TRANS".FormatString(_connectionString)) as IDbTransaction;
                    _selfManage = false;
                }
            }
            else
            {
                
            }

            _command = CreateCommand(_sql, _connection);
            _command.CommandType = GetCommandType(_sql);
            if (_trans != null)
                _command.Transaction = _trans;

            DeriveParameters(_command);

            return base.Initialize();
        }

        protected abstract IDbConnection CreateConnection(string connectionString);
        protected abstract IDbCommand CreateCommand(string sql, IDbConnection conn);
        protected abstract IDbDataParameter CreateDataParameter(string paramName);

        protected override ChoETLObject ExecuteTask(ChoETLObject item)
        {
            PopulateParameters(item);
            _command.ExecuteNonQuery();
            return item;
        }

        protected override void Commit()
        {
            if (_selfManage)
            {
                if (_trans != null)
                    _trans.Commit();
                _connection.Close();
            }
            base.Commit();
        }

        protected override void Rollback()
        {
            if (_selfManage)
            {
                if (_trans != null)
                    _trans.Rollback();
                _connection.Close();
            }
            base.Rollback();
        }

        #endregion ChoETLFileLoadTask Overridables

        protected virtual CommandType GetCommandType(string sql)
        {
            if (sql.StartsWith("INSERT", true, Thread.CurrentThread.CurrentCulture)
                || sql.StartsWith("DELETE", true, Thread.CurrentThread.CurrentCulture)
                || sql.StartsWith("UPDATE", true, Thread.CurrentThread.CurrentCulture))
                return CommandType.Text;
            else if (sql.StartsWith("SELECT", true, Thread.CurrentThread.CurrentCulture))
                throw new ChoETLException("Invalid command text passed.");
            else
                return CommandType.StoredProcedure;
        }

        protected virtual void DeriveParameters(IDbCommand command)
        {
            string cmdText = command.CommandText;
            Regex reg = new Regex(@"@\w+");

            foreach (Match match in reg.Matches(cmdText))
                command.Parameters.Add(CreateDataParameter(match.Value));
        }

        protected virtual void PopulateParameters(ChoETLObject item)
        {
            if (_command.Parameters == null)
                return;

            if (item is ChoETLScalarObject)
            {
                KeyValuePair<string, object>[] parameters = ((ChoETLScalarObject)item).Value as KeyValuePair<string, object>[];

                if (parameters != null)
                {
                    foreach (IDbDataParameter dbDataParameter in (IEnumerable)_command.Parameters)
                    {
                        string propertyName = dbDataParameter.ParameterName.Replace("@", String.Empty);
                        var p = (from c in parameters
                                 where c.Key == propertyName
                                 select c.Value).FirstOrDefault();
                        if (p != null)
                            dbDataParameter.Value = p;
                    }
                }
            }
        }
    }

}
